Understanding Views, Stored Procedures, and Query Considerations

Course: Microsoft Power BI Training

Topic: Understanding Views, Stored Procedures, and Query Considerations

Course Link: Microsoft Power BI Training – JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction

Power BI is widely used to analyze data from relational databases, cloud platforms, Excel workbooks, and other sources. When working with databases such as Microsoft SQL Server, developers and data analysts often use views, stored procedures, and SQL queries to prepare and retrieve information for reporting.

Understanding these database objects helps Power BI developers build reliable data models, reduce unnecessary data processing, improve refresh performance, and maintain consistent business logic. It is especially important when connecting Power BI to enterprise databases containing millions of records.

A view presents data through a predefined SQL query. A stored procedure contains a sequence of database statements that can accept parameters and perform operations. A query is an instruction used to retrieve or manipulate data. Each has a different purpose and may behave differently depending on the database connector and Power BI storage mode.

In this topic, you will learn how views and stored procedures work, how to use them with Power BI, how query folding affects performance, and what to consider when designing database queries for analytical reports.

1.1 Learning Objectives

• Understand the purpose of database views and stored procedures.
• Differentiate tables, views, stored procedures, and SQL queries.
• Create and use SQL Server views for Power BI reporting.
• Understand stored procedure parameters and result sets.
• Connect Power BI Desktop to SQL Server.
• Understand Import mode and DirectQuery mode.
• Learn query folding and its role in Power BI performance.
• Identify query design practices that improve refresh and report responsiveness.
• Recognize limitations involving stored procedures, DirectQuery, and incremental refresh.
• Troubleshoot common database connectivity and query errors.

2. Understanding Database Tables, Views, Stored Procedures, and Queries

2.1 What Is a Database Table?

A database table stores data in rows and columns. Each row represents a record, and each column represents an attribute of that record.

For example, a Sales table might contain order identifiers, order dates, customer identifiers, product identifiers, quantities, and sales amounts.

CREATE TABLE Sales (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    ProductID INT,
    CustomerID INT,
    Quantity INT,
    SalesAmount DECIMAL(12,2)
);

This example illustrates a SQL Server table definition. In a real database, constraints, indexes, relationships, and data types should be designed according to the business requirements.

2.2 What Is a Database View?

A view is a named database object defined by a SQL query. It presents data from one or more tables or other views as a virtual table.

A standard view usually stores its query definition rather than maintaining a separate physical copy of its result. When a query references the view, the database generally expands or incorporates the view definition into the execution plan. Actual execution and optimization depend on the database engine.

For example, a SalesSummary view might expose order dates, product names, regions, and sales amounts while hiding unnecessary columns from the report consumer.

2.3 What Is a Stored Procedure?

A stored procedure is a named database program containing one or more SQL statements and, where supported, procedural logic. It can accept input parameters, return result sets, use output parameters, and perform database operations.

Stored procedures are useful for encapsulating complex logic, applying consistent rules, and centralizing database operations. However, they are not automatically faster than ordinary queries. Performance depends on the execution plan, indexing, data volume, parameter values, and implementation.

2.4 What Is a SQL Query?

A SQL query is an instruction written in Structured Query Language to retrieve or manipulate database information. A SELECT query retrieves data, while INSERT, UPDATE, and DELETE statements modify data.

SELECT
    OrderID,
    OrderDate,
    SalesAmount
FROM dbo.Sales
WHERE OrderDate >= '2026-01-01';

This query returns selected columns from the Sales table for orders on or after the specified date.

2.5 Comparison of Database Objects

3. Understanding Views in SQL Server

3.1 Why Are Views Used?

Views allow database developers to present a useful, controlled representation of underlying data. Instead of repeating the same joins and filters in every reporting query, developers can create a view and allow multiple reports to use it.

Common reasons for using views include:

• Simplifying complex SQL joins.
• Presenting business-friendly column names.
• Limiting the columns and rows exposed to consumers.
• Centralizing reusable query logic.
• Separating reporting logic from underlying table structures.
• Providing a consistent source for Power BI and other reporting applications.

3.2 Creating a Simple View

Suppose the Sales table contains more columns than a sales dashboard requires. A view can expose only the necessary fields.

CREATE VIEW dbo.vw_SalesReport
AS
SELECT
    OrderID,
    OrderDate,
    ProductID,
    CustomerID,
    Quantity,
    SalesAmount
FROM dbo.Sales;
GO

The view can then be queried like a table:

SELECT
    OrderID,
    OrderDate,
    SalesAmount
FROM dbo.vw_SalesReport;

In SQL Server, CREATE VIEW is normally executed as a separate batch. The GO command is a client-tool batch separator rather than a SQL Server engine statement.

3.3 Creating a View with a JOIN

A reporting view can combine transaction data with descriptive information from related tables.

CREATE VIEW dbo.vw_SalesDetails
AS
SELECT
    s.OrderID,
    s.OrderDate,
    p.ProductName,
    p.Category,
    c.CustomerName,
    c.Region,
    s.Quantity,
    s.SalesAmount
FROM dbo.Sales AS s
INNER JOIN dbo.Products AS p
    ON s.ProductID = p.ProductID
INNER JOIN dbo.Customers AS c
    ON s.CustomerID = c.CustomerID;
GO

This view presents product and customer attributes alongside each sales transaction. It can simplify Power BI data ingestion by providing a ready-to-use dataset.

3.4 Creating an Aggregated View

When a report only needs summarized information, a database view can aggregate detailed transactions.

CREATE VIEW dbo.vw_RegionalSales
AS
SELECT
    c.Region,
    SUM(s.SalesAmount) AS TotalSales,
    SUM(s.Quantity) AS TotalQuantity,
    COUNT(DISTINCT s.OrderID) AS OrderCount
FROM dbo.Sales AS s
INNER JOIN dbo.Customers AS c
    ON s.CustomerID = c.CustomerID
GROUP BY c.Region;
GO

This view returns one row per region rather than one row per sales transaction. It may reduce the amount of data transferred when the report only needs regional summaries. However, it cannot replace a detailed transaction source when users need order-level analysis.

3.5 Standard Views Versus Indexed Views

A standard SQL Server view normally stores its definition, while an indexed view can store a materialized result through a qualifying index. Indexed views have specific requirements and maintenance costs.

Do not assume that creating an indexed view will automatically improve every Power BI query. Test performance using the actual workload and database execution plans.

4. Using Views in Power BI

4.1 Connect Power BI Desktop to SQL Server

1. Open Power BI Desktop.
2. Select Home and then Get data.
3. Choose SQL Server database.
4. Enter the server name and, if necessary, the database name.
5. Select Import or DirectQuery as the storage mode.
6. Choose the appropriate authentication method.
7. Open Navigator after connecting.
8. Locate the required view, such as dbo.vw_SalesDetails.
9. Select the view and choose Load or Transform Data.
10. Review the imported data, column names, and data types.

4.2 Import Mode with a View

In Import mode, Power BI loads the result of the selected view into its semantic model. The data is stored in the model and can be refreshed to retrieve updated results.

Advantages include:

• Fast report interaction for many analytical workloads.
• Flexible modeling and DAX calculations.
• Reduced dependence on the database for every visual interaction.
• Ability to combine data from multiple supported sources.

Considerations include model size, refresh duration, data freshness, and the resources required to import the selected data.

4.3 DirectQuery Mode with a View

In DirectQuery mode, Power BI sends queries to the underlying source when data is requested. A view can provide a reusable logical source, but the generated query and its execution plan still determine performance.

Advantages include:

• Reduced need to import the entire source dataset.
• Access to current source data according to query and caching behavior.
• Centralized data preparation in the database.

Considerations include query latency, source-system workload, database indexing, query folding, and DirectQuery modeling limitations.

4.4 When Should You Use a View?

• When several reports need the same joins and column definitions.
• When you want a consistent, documented reporting interface.
• When unnecessary source columns should not be exposed to a report.
• When source-side filtering or aggregation is beneficial.
• When you want to keep reusable transformation logic in the database.

5. Understanding Stored Procedures

5.1 Purpose of Stored Procedures

Stored procedures package SQL statements and procedural logic into a reusable database object. They are commonly used for application workflows, parameterized reporting, data processing, and administrative tasks.

Typical benefits include:

• Reusable database logic.
• Parameter support.
• Centralized implementation of business rules.
• Permission management through controlled execution rights.
• Potential execution-plan reuse.
• Support for complex operations such as temporary tables and conditional logic.

These benefits depend on the database engine, procedure design, execution plan, and how the calling application executes the procedure.

5.2 Creating a Basic Stored Procedure

The following SQL Server example returns sales records for a specified date range.

CREATE PROCEDURE dbo.usp_GetSalesByDate
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderID,
        OrderDate,
        ProductID,
        CustomerID,
        Quantity,
        SalesAmount
    FROM dbo.Sales
    WHERE OrderDate >= @StartDate
      AND OrderDate < @EndDate;
END;
GO

The procedure accepts two parameters. Using an exclusive end date makes it convenient to include all timestamps or dates before the next day when the source field is a datetime type. If the source column is a DATE, the same range pattern works for an end date treated as exclusive.

5.3 Executing a Stored Procedure

EXEC dbo.usp_GetSalesByDate
    @StartDate = '2026-01-01',
    @EndDate = '2026-02-01';

This execution requests sales records from January 1, 2026, up to but not including February 1, 2026.

5.4 Stored Procedures with Aggregation

A procedure can return aggregated data for a selected date range.

CREATE PROCEDURE dbo.usp_GetSalesSummary
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        COUNT(DISTINCT OrderID) AS OrderCount,
        SUM(Quantity) AS TotalQuantity,
        SUM(SalesAmount) AS TotalSales
    FROM dbo.Sales
    WHERE OrderDate >= @StartDate
      AND OrderDate < @EndDate;
END;
GO

This procedure returns a summary for the requested period. If the report needs sales by region or category, the procedure can be designed to group the records by the relevant dimensions.

5.5 Stored Procedures with Multiple Result Sets

A stored procedure can return more than one result set, depending on its implementation. However, data connectors do not all handle multiple result sets in the same way.

For Power Query native SQL execution, Microsoft documents that when a statement produces multiple result sets, only the first is returned. For a predictable Power BI data source, prefer a procedure or query that returns one clearly defined result set with stable column names and data types.

5.6 Stored Procedures and Side Effects

A stored procedure is not necessarily read-only. It may insert, update, or delete records, change database objects, or execute other operations. A query intended for reporting should avoid unintended side effects.

Use a dedicated, least-privilege database account for Power BI. Grant only the permissions required to read approved data or execute specifically approved reporting procedures. Do not give a reporting connection broad write or administrative permissions unnecessarily.

6. Using Stored Procedures with Power BI

6.1 Stored Procedures in Power BI Desktop

Some relational connectors allow native SQL statements to be executed from Power Query, including stored procedure execution for supported workflows. The exact options depend on the connector, storage mode, and operation.

A typical Import-mode workflow is:

1. Open Power BI Desktop and connect to the database.
2. Open Power Query Editor.
3. Choose the database source or use a supported native-query option.
4. Provide a valid SQL statement or procedure execution where supported.
5. Supply parameter values safely.
6. Review the returned columns and data types.
7. Apply transformations that are supported for the chosen source and query method.
8. Load the data and test refresh behavior.

6.2 Example of a Native SQL Call

The following Power Query M example illustrates a SQL Server stored procedure call using a native-query function. It assumes the database connection is already configured and that the procedure accepts the specified parameters.

let
    Source = Sql.Database(
        "YourServerName",
        "YourDatabaseName"
    ),
    SalesData = Value.NativeQuery(
        Source,
        "EXEC dbo.usp_GetSalesByDate
            @StartDate = @StartDate,
            @EndDate = @EndDate",
        [
            StartDate = #date(2026, 1, 1),
            EndDate = #date(2026, 2, 1)
        ]
    )
in
    SalesData

Adapt the server, database, procedure name, parameter syntax, and parameter values to the actual connector and environment. Test the statement with the intended driver and authentication method before relying on scheduled refresh.

Important: Using Value.NativeQuery does not guarantee that every later Power Query step can fold back to the database. Native SQL and stored procedure usage also have important restrictions in DirectQuery and incremental-refresh scenarios.

6.3 Stored Procedures and DirectQuery

Power BI DirectQuery relies on generating source queries that can execute against the underlying system. For SQL Server DirectQuery tables, native SQL queries must meet DirectQuery requirements. Common table expressions and stored procedure calls are not supported as native DirectQuery table queries in the same way as supported SELECT statements.

If the report needs DirectQuery, consider a database view or a foldable SELECT query instead of relying on a stored procedure as the table source.

6.4 Stored Procedures and Incremental Refresh

Incremental refresh depends on filtering the source data efficiently using the required date parameters. A native SQL query can prevent Power Query from folding later transformations and can make incremental-refresh processing unsuitable or inefficient.

For large datasets requiring incremental refresh, a well-designed view or a foldable parameterized query is often a better starting point. Validate query folding and incremental-refresh requirements before selecting a procedure-based design.

7. Views Versus Stored Procedures

A view is often a good choice for a reusable Power BI source, especially when report queries must be composable and foldable. A stored procedure is useful when procedural logic or explicit parameters are required, but compatibility with the intended Power BI workflow must be checked.

8. Understanding Query Considerations in Power BI

Query design affects how much data is transferred, how much work the database performs, how much work Power Query performs, and how quickly visuals respond.

8.1 Select Only Required Columns

Avoid retrieving every column when the report needs only a few fields. Unnecessary columns increase data transfer, model size, and sometimes query execution time.

Less efficient query:

SELECT *
FROM dbo.Sales;

More focused query:

SELECT
    OrderDate,
    ProductID,
    Quantity,
    SalesAmount
FROM dbo.Sales;

The second query retrieves only the columns needed for the example analysis. The appropriate column list should be based on the actual report requirements.

8.2 Filter Data at the Source

When appropriate, filter records at the database rather than retrieving all rows and discarding most of them in Power BI.

SELECT
    OrderID,
    OrderDate,
    SalesAmount
FROM dbo.Sales
WHERE OrderDate >= '2026-01-01'
  AND OrderDate < '2027-01-01';

Source-side filtering can reduce the amount of data transferred. For reusable models that must cover multiple periods, use appropriate parameters or a broader source view and allow Power BI to apply foldable filters.

8.3 Avoid Unnecessary Transformations

Some transformations can be executed by the source database, while others may need to be performed by the Power Query engine. Source-side transformations are often beneficial for large relational datasets when the database can execute them efficiently.

• Remove unused columns early.
• Filter unnecessary rows as early as practical.
• Avoid repeatedly performing expensive transformations.
• Keep transformations simple and compatible with query folding where possible.
• Move stable, complex business transformations into a database view or prepared data layer when appropriate.

8.4 Use Appropriate Data Types

Correct data types improve data quality and can help the database execute filters and joins efficiently. For example, joining integer identifiers to text identifiers may require conversions and may prevent efficient use of indexes.

• Use date or datetime types for date fields.
• Use appropriate integer or decimal types for numeric fields.
• Use text for descriptive attributes and codes that are genuinely textual.
• Ensure join columns have compatible types.
• Avoid wrapping indexed columns in unnecessary conversion functions in WHERE conditions.

8.5 Avoid Unnecessary DISTINCT and Sorting

DISTINCT removes duplicate result rows, while ORDER BY sorts query results. Both may add processing overhead, depending on the execution plan and database engine.

Use DISTINCT when unique rows are part of the requirement. Avoid sorting source data if the report model or visual will apply its own sorting and the source order is not required.

9. Understanding Query Folding

9.1 What Is Query Folding?

Query folding is the process by which Power Query translates supported transformation steps into a query that can be executed by the underlying data source. For relational sources, this commonly means generating SQL that performs filtering, selecting columns, joining, or aggregating data.

Query folding can reduce the amount of data transferred and allow the source database to perform work using its own query optimizer and indexes.

9.2 Example of Query Folding

Suppose a Power Query transformation selects only recent sales records and keeps four columns. When the steps can fold, Power Query may generate SQL similar to the following:

SELECT
    OrderDate,
    ProductID,
    Quantity,
    SalesAmount
FROM dbo.Sales
WHERE OrderDate >= '2026-01-01';

The actual SQL generated depends on the connector and the transformations. This example illustrates the concept rather than a guaranteed generated statement.

9.3 Why Query Folding Matters

• Reduces unnecessary data transfer.
• Allows the source engine to process supported filters and transformations.
• Can improve refresh time for large datasets.
• Is required for many DirectQuery transformation scenarios.
• Helps make some incremental-refresh designs efficient.

9.4 Checking Query Folding

1. Open Power Query Editor in Power BI Desktop.
2. Select a query connected to a supported relational source.
3. Right-click a transformation step where applicable.
4. Look for View Native Query if the connector exposes it.
5. Inspect the generated SQL when available.
6. Test the refresh and query behavior using representative data volumes.

The absence of View Native Query does not always prove that folding has stopped; connector capabilities and Power Query versions differ. Use supported query-plan or diagnostics features where available.

9.5 Common Causes of Broken Query Folding

• Using transformations that the connector cannot translate to the source.
• Applying custom functions or procedural transformations unsupported by the source.
• Using native SQL in a way that prevents subsequent steps from folding.
• Combining sources with incompatible capabilities.
• Performing complex transformations in an unsuitable sequence.

For official guidance, read Query folding guidance in Power BI Desktop.

10. Query Performance and Database Indexing

10.1 What Is a Database Index?

An index is a database structure that can help locate records without scanning an entire table. Index design should support common query patterns, including filters, joins, and sorts.

For example, a query that frequently filters sales records by OrderDate may benefit from an appropriate index on that column. The correct design depends on table size, data distribution, existing indexes, and the workload.

10.2 Example Index

CREATE INDEX IX_Sales_OrderDate
ON dbo.Sales (OrderDate);

This example creates a nonclustered index on OrderDate in SQL Server. It is illustrative only; index changes should be tested by a database administrator because indexes require storage and add overhead to data modifications.

10.3 Indexing for Joins

Queries that join Sales to Products by ProductID and Sales to Customers by CustomerID may benefit from appropriate indexes on join columns, depending on the existing schema and execution plan.

SELECT
    s.OrderID,
    p.ProductName,
    c.Region,
    s.SalesAmount
FROM dbo.Sales AS s
INNER JOIN dbo.Products AS p
    ON s.ProductID = p.ProductID
INNER JOIN dbo.Customers AS c
    ON s.CustomerID = c.CustomerID
WHERE s.OrderDate >= '2026-01-01';

Check whether the relevant join keys are primary keys, unique keys, or indexed foreign-key columns where appropriate. The best index strategy should be based on actual execution plans rather than assumptions.

10.4 Examine Execution Plans

An execution plan describes how the database intends to execute a query. It can show scans, seeks, joins, sorts, aggregations, estimated row counts, and other operations.

When investigating slow queries:

• Use SQL Server Management Studio or supported database monitoring tools.
• Review actual execution plans when safe and appropriate.
• Look for expensive scans, sorts, or joins.
• Check whether estimates differ significantly from actual row counts.
• Inspect index usage and statistics.
• Compare execution times with representative parameters and data volumes.

11. Query Considerations for Import Mode

Import mode stores data in the Power BI semantic model. Queries are commonly executed when the model is refreshed rather than for every visual interaction.

11.1 Import Mode Recommendations

• Import only the columns and rows required for analysis.
• Use query folding for relational sources where possible.
• Remove unnecessary intermediate columns and tables.
• Keep Power Query transformations maintainable.
• Consider pre-aggregating data when detailed records are not needed.
• Monitor model size, refresh duration, and resource use.
• Use incremental refresh when the model, source, and query design support it.

11.2 When Import Mode Is Suitable

• When fast interactive reporting is a priority.
• When source data can be refreshed periodically.
• When the model fits within the applicable size and capacity limits.
• When flexible relationships and calculations are required.

12. Query Considerations for DirectQuery

DirectQuery keeps data in the underlying source and sends queries to that source as users interact with the report. This can be useful for large datasets or when source-level access is required, but it makes source query performance especially important.

12.1 DirectQuery Recommendations

• Keep the database responsive under expected report usage.
• Use appropriate indexes and efficient join conditions.
• Ensure Power Query transformations can fold into supported source queries.
• Reduce unnecessary visuals and complex visual interactions.
• Avoid unnecessary high-cardinality columns in visuals and slicers.
• Test report performance with realistic filters and concurrent users.
• Consider Import or a composite model if DirectQuery limitations prevent the desired design.

12.2 Stored Procedure Limitations in DirectQuery

SQL Server DirectQuery requires a supported SELECT-based query pattern for native table queries. A stored procedure call or a query using a common table expression cannot be treated as an ordinary foldable DirectQuery table source in the same way.

For this reason, use a view or supported SELECT query as the source when DirectQuery is required. If a stored procedure is necessary, evaluate whether Import mode or another supported architecture better fits the reporting requirement.

Read DirectQuery in Power BI for further considerations and limitations.

13. Parameters and Dynamic Filtering

13.1 Why Use Parameters?

Parameters allow queries or procedures to use configurable values, such as date ranges, regions, or business units. They can help make data retrieval reusable without hardcoding every value into the query.

13.2 SQL Server Parameter Example

CREATE PROCEDURE dbo.usp_GetRegionalSales
    @Region NVARCHAR(50),
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        s.OrderID,
        s.OrderDate,
        c.Region,
        s.SalesAmount
    FROM dbo.Sales AS s
    INNER JOIN dbo.Customers AS c
        ON s.CustomerID = c.CustomerID
    WHERE c.Region = @Region
      AND s.OrderDate >= @StartDate
      AND s.OrderDate < @EndDate;
END;
GO

This procedure filters by region and date range. The actual Power BI implementation must support the chosen parameter mechanism and refresh configuration.

13.3 Safe Parameter Handling

• Prefer parameterized queries rather than concatenating untrusted input into SQL statements.
• Use the correct data type for each parameter.
• Validate date ranges and other input values.
• Test how parameters behave during refresh in the Power BI Service.
• Do not assume that report slicers automatically pass values to a stored procedure used as a data source.

Power BI report slicers generally filter the semantic model. They do not automatically re-execute an arbitrary Import-mode stored procedure with a different parameter value for each slicer selection.

14. Security Considerations

14.1 Least-Privilege Access

The database account used by Power BI should have only the permissions required for its task. For a read-only reporting workflow, that often means access to approved tables or views and, where necessary, execute permission on approved read-only procedures.

• Avoid using database administrator accounts for routine report refresh.
• Restrict access to sensitive columns and rows.
• Use approved authentication and credential storage.
• Review database permissions when a report is shared with new users.
• Keep secrets out of SQL text, M scripts, and report files.

14.2 Views Are Not a Complete Security Boundary by Themselves

A view can limit the data presented to a consumer, but security depends on database permissions and the broader security configuration. Confirm that users cannot bypass the intended view through other grants on the underlying tables.

14.3 Stored Procedures and Permissions

Stored procedures can provide controlled access to approved operations. Carefully configure execute permissions and execution context, and verify which database operations a procedure can perform.

14.4 Native SQL Risks

Native SQL can potentially execute statements beyond simple data retrieval. Use trusted query text, validate parameters, and apply least-privilege permissions. Avoid granting unnecessary write permissions to a reporting identity.

15. Common Problems and Troubleshooting

16. Best Practices for Working with Views and Stored Procedures

16.1 View Design

• Use clear and consistent names, such as vw_SalesReport.
• Expose only the fields required by the reporting use case.
• Document the purpose of the view and the meaning of its columns.
• Avoid unnecessary nested views and overly complex joins.
• Test the view against realistic data volumes.
• Ensure that joins do not unintentionally duplicate records.

16.2 Stored Procedure Design

• Use meaningful procedure names, such as usp_GetSalesByDate.
• Define parameter data types explicitly.
• Return predictable column names and data types.
• Prefer one clearly defined result set for data retrieval.
• Handle errors and boundary conditions appropriately.
• Avoid unnecessary operations that modify data in reporting procedures.
• Test performance for representative parameter values.

16.3 Query Design

• Select only required columns.
• Filter unnecessary rows as early as practical.
• Use efficient joins and appropriate indexes.
• Avoid unnecessary sorting and duplicate elimination.
• Keep date filters compatible with source indexes where possible.
• Prefer query folding for supported transformations.
• Use source-side aggregation when the report does not need detailed records.
• Test refresh and report performance after changes.

17. Practical Project: Build a Power BI Report Using a SQL View

17.1 Project Objective

Create a SQL Server view for sales reporting, connect it to Power BI, build a report, and evaluate query behavior.

17.2 Sample Database Structure

Sales
-----
OrderID
OrderDate
ProductID
CustomerID
Quantity
SalesAmount

Products
--------
ProductID
ProductName
Category

Customers
---------
CustomerID
CustomerName
Region

17.3 Project Steps

1. Create or identify the Sales, Products, and Customers tables.
2. Verify primary keys and the relationships between the tables.
3. Create the dbo.vw_SalesDetails view.
4. Test the view directly in SQL Server.
5. Connect Power BI Desktop to the SQL Server database.
6. Import the view into Power BI.
7. Check the data types and remove unused columns.
8. Create a Total Sales measure.
9. Create a bar chart showing Total Sales by Region.
10. Create a line chart showing sales over time.
11. Add date and region slicers.
12. Publish the report if required.
13. Configure and test refresh.
14. Measure refresh duration and examine query folding where supported.
15. Document the source object, security permissions, and refresh process.

17.4 Example Measure

Total Sales =
SUM(vw_SalesDetails[SalesAmount])

This measure calculates total sales based on the current filter context. Validate the results against a database query using equivalent filters.

17.5 Extension Exercise: Stored Procedure

Create a stored procedure that returns sales records for a supplied date range. Test it in SQL Server, then evaluate whether an Import-mode workflow can use it reliably. Compare its limitations with a view-based approach, especially if DirectQuery or incremental refresh is required.

18. Exercises for Practice

Exercise 1: Create a Simple View

Create a view that returns OrderID, OrderDate, and SalesAmount from a Sales table. Connect Power BI to the view and create a total sales card.

Exercise 2: Create a Joined View

Join Sales, Products, and Customers to create a reporting view. Verify that the joins do not create unintended duplicate sales records.

Exercise 3: Create a Stored Procedure

Create a stored procedure that accepts StartDate and EndDate parameters. Test boundary dates and confirm that the procedure returns the expected rows.

Exercise 4: Compare Query Approaches

Compare a direct SELECT query with a view-based query. Review the execution plans and determine whether the optimizer uses similar operations.

Exercise 5: Investigate Query Folding

Import a SQL Server view into Power Query. Apply a filter and remove unused columns. Check whether the transformations can fold to the source.

Exercise 6: Diagnose a Slow Query

Use an appropriate test environment to compare query performance before and after a justified index change. Review the actual execution plan and validate the results.

19. Interview Questions and Answers

Q1. What is a database view?

Answer: A view is a named database object defined by a query. It presents data from one or more tables or views through a reusable relational interface.

Q2. What is a stored procedure?

Answer: A stored procedure is a named database routine that can contain SQL statements and procedural logic. It may accept parameters, return result sets, and perform database operations.

Q3. What is the difference between a view and a stored procedure?

Answer: A view exposes query results in a form that can be queried like a table, while a stored procedure executes a routine and can accept parameters or perform more complex operations.

Q4. Why use views in Power BI?

Answer: Views can centralize joins, simplify source queries, provide consistent reporting fields, and limit the data exposed to report developers.

Q5. Can a standard SQL Server view accept parameters?

Answer: No. Standard SQL Server views do not accept parameters directly. Parameterized logic can instead be implemented using a stored procedure, a parameterized query, or another suitable design.

Q6. Can Power BI execute stored procedures?

Answer: Stored procedure execution can be supported in some connector and Import-mode workflows through native SQL execution. Compatibility, parameter handling, refresh behavior, and security must be tested.

Q7. Are stored procedures supported as DirectQuery table sources?

Answer: SQL Server DirectQuery requires supported query patterns, and stored procedure calls are not supported as ordinary foldable DirectQuery table queries. A view or supported SELECT query is often more suitable.

Q8. What is query folding?

Answer: Query folding is the translation of supported Power Query transformations into operations executed by the underlying data source.

Q9. Why is query folding important?

Answer: It can reduce data transfer and allow the source engine to perform filters, joins, and transformations. It is particularly important for many DirectQuery and large-data refresh scenarios.

Q10. What happens when query folding stops?

Answer: Later transformations may need to be performed by the Power Query engine instead of the source, which can increase processing and data-transfer costs. The effect depends on the query and storage mode.

Q11. How can SQL query performance be improved?

Answer: Retrieve only necessary columns and rows, optimize joins, use suitable indexes, review execution plans, avoid unnecessary transformations, and test with representative data.

Q12. What is the difference between Import and DirectQuery?

Answer: Import loads data into the Power BI semantic model and updates it through refresh. DirectQuery sends queries to the underlying source as required by report interactions.

Q13. Why can a view-based query be slow?

Answer: The view may contain expensive joins, aggregations, filters, or expressions. Its performance depends on the underlying query, indexes, data distribution, and execution plan.

Q14. What are the risks of SELECT *?

Answer: It can retrieve unnecessary columns, increase data transfer, complicate schema changes, and expose fields that the report does not require.

Q15. Why can a stored procedure return unexpected results?

Answer: Causes include incorrect parameters, date-boundary mistakes, unexpected joins, multiple result sets, and logic errors in the procedure.

Q16. What is an execution plan?

Answer: An execution plan shows how a database engine intends to perform a query, including access methods, joins, sorting, and aggregation operations.

Q17. Why should reporting accounts use least privilege?

Answer: Least privilege limits the potential impact of mistakes or misuse by granting only the database permissions required for the reporting task.

Q18. Can a stored procedure automatically receive Power BI slicer selections?

Answer: Not automatically in a typical Import-mode source query. Report slicers generally filter the loaded semantic model; dynamically passing slicer values to a stored procedure requires a specifically designed supported architecture.

Q19. What is an indexed view?

Answer: An indexed view is a qualifying SQL Server view whose result is stored through an index. It can improve certain workloads but has specific requirements and maintenance costs.

Q20. Which is usually preferable for a reusable Power BI database source: a view or a stored procedure?

Answer: A view is often preferable when Power BI needs a composable, foldable relational source. A stored procedure can be useful for parameterized or procedural workflows, but its compatibility with the selected storage mode and refresh design must be verified.

20. Quick Revision Checklist

• Understand tables, views, stored procedures, and SQL queries.
• Know how to create a standard SQL Server view.
• Know how to create a stored procedure with parameters.
• Understand how Power BI connects to database views.
• Differentiate Import mode and DirectQuery mode.
• Recognize stored procedure limitations in DirectQuery.
• Understand query folding and its performance benefits.
• Retrieve only the required rows and columns.
• Use appropriate data types, joins, and indexes.
• Review execution plans when diagnosing slow queries.
• Validate parameters and result sets.
• Configure permissions using the least-privilege principle.
• Test refresh behavior in Power BI Desktop and the Power BI Service.
• Document data sources and reusable database logic.

21. Official Learning Resources

• Query folding guidance in Power BI Desktop – Learn how Power Query transformations can be executed by the source.
• DirectQuery in Power BI – Understand DirectQuery behavior, performance, and limitations.
• Power BI guidance documentation – Explore recommendations for modeling, transformations, and report performance.
• Data retrieval guidance for paginated reports – Learn about query result size, source-side processing, and stored procedures in paginated-report workflows.
• Microsoft Support: SQL Server views and stored procedures – Review basic concepts and examples of SQL Server views and stored procedures.

22. Conclusion

Views, stored procedures, and SQL queries are important tools for preparing database data for Power BI. Views provide reusable relational data sources, stored procedures encapsulate parameterized and procedural logic, and SQL queries define the data retrieved from the database.

Choosing the right approach requires more than knowing SQL syntax. Power BI developers must consider query folding, Import and DirectQuery behavior, refresh requirements, indexing, execution plans, parameter handling, and security permissions. A query that works in a database management tool may not be suitable for every Power BI storage mode or scheduled-refresh scenario.

For many reporting models, a well-designed view or foldable SELECT query is a strong starting point. Stored procedures remain useful where their functionality is required, provided their limitations and refresh behavior are understood and tested.

Explore the course: Microsoft Power BI Training at JustAcademy

Register for a demo: Register for a Course Demo

Visit JustAcademy: https://www.justacademy.co/